In [3]:
from ris import db2 #library designed for SQL database connection and querying
from IPython.display import clear_output
import datetime
from datetime import date
import pandas as pd
import os
import decimal
from sqlalchemy import create_engine
from ris import pg_import_export_shps as shp
clear_output()
timestamp = datetime.datetime.now().strftime('%Y-%m-%d %H:%M')
print 'Notebook run: {}'.format(timestamp)
print os.getcwd()
# %load_ext sql
In [5]:
#Database connections
cdb = db2.PostgresDb('DOTDEVRHPGSQL01', 'CRASHDATA', quiet = True)
Query containing all street end nodes and geometries¶
In [3]:
dead_ends = cdb.query("""select tot.nodeid, n2.geom from (
select coalesce(nodeidto, nodeidfrom) nodeid from (
select distinct l.nodeidfrom --table containing all distinct nodeidfrom nodes
from lion l
join node n on --joins lion to node table (necessary to exclude virtual intersections)
n.nodeid = l.nodeidfrom::int
where l.rb_layer in ('R', 'B', 'N') and l.featuretyp not in ('1', '2', '3', '7', 'A', 'F') --Removes all other elements that are not real streets
and n.vintersect is null -- Removes all virtual intersections
) f
full join -- Joins both nodeidfrom table to nodeidto table on nodeidfrom=nodeidto
(
select distinct l.nodeidto --table containing all distinct nodeidto nodes
from lion l
join node n on --joins lion to node table (necessary to exclude virtual intersections)
n.nodeid = l.nodeidto::int
where l.rb_layer in ('R', 'B', 'N') and l.featuretyp not in ('1', '2', '3', '7', 'A', 'F') --Removes all other elements that are not real streets
and n.vintersect is null -- Removes all virtual intersections
) t
on f.nodeidfrom=t.nodeidto
where f.nodeidfrom is null or t.nodeidto is null) tot -- only considers nodes where nodeidfrom and nodeid don't match.
join node n2 -- joins dead_end table back to node table to retrieve geometries.
on tot.nodeid::int=n2.nodeid
""")
In [ ]:
dead_ends = cdb.query("""
select ans.nodeid, n2.geom from (
--This query returns a table containing distinct from and to nodeids and geometries that are dead ends
select nodeid1 nodeid from (
--This subquery returns a table containing distinct from and to nodeids that are dead ends
select coalesce(nodeidto, nodeidfrom) nodeid1 from (
--This subquery returns a table containing all distinct from and to nodeids that are not the from and to nodes
--for multiple segments
select distinct nodeidfrom from(
select distinct l.nodeidfrom, l.street, l.geom from lion l
join node n
on n.nodeid = l.nodeidfrom::int
where rb_layer in ('R', 'B', 'N')
and featuretyp not in ('1', '2', '3', '7','8', 'A', 'F') --Removes all other elements that are not real streets
and n.vintersect is null ) d -- Removes all virtual intersections
group by nodeidfrom
having count(nodeidfrom) = 1
) f
full join
(
select nodeidto from(
select distinct l.nodeidto, l.street, l.geom
from lion l
join node n
on n.nodeid = l.nodeidto::int
where rb_layer in ('R', 'B', 'N')
and featuretyp not in ('1', '2', '3', '7','8', 'A', 'F') --Removes all other elements that are not real streets
and n.vintersect is null ) d -- Removes all virtual intersections
group by nodeidto
having count(nodeidto) =1
) t
on f.nodeidfrom=t.nodeidto
where f.nodeidfrom is null or t.nodeidto is null) tot
join
(
--This subquery returns a table containing all of the from and to nodeids that do not match. This query
--joins on exclusion nodeidfrom and nodeidto
select coalesce(nodeidto, nodeidfrom) nodeid2 from (
select distinct l.nodeidfrom --table containing all distinct nodeidfrom nodes
from lion l
join node n on
n.nodeid = l.nodeidfrom::int
where l.rb_layer in ('R', 'B', 'N') and l.featuretyp not in ('1', '2', '3', '7','8', 'A', 'F') --Removes all other elements that are not real streets
and n.vintersect is null -- Removes all virtual intersections
) f
full join
(
select distinct l.nodeidto --table containing all distinct nodeidto nodes
from lion l
join node n on
n.nodeid = l.nodeidto::int
where l.rb_layer in ('R', 'B', 'N') and l.featuretyp not in ('1', '2', '3', '7','8', 'A', 'F') --Removes all other elements that are not real streets
and n.vintersect is null -- Removes all virtual intersections
) t
on f.nodeidfrom=t.nodeidto
where f.nodeidfrom is null or t.nodeidto is null) tot2
on tot.nodeid1=tot2.nodeid2) ans
join node n2
on ans.nodeid::int=n2.nodeid
order by ans.nodeid
""")
Dataframe containing all street end nodes and geometries¶
In [4]:
dead_ends=pd.DataFrame(dead_ends.data,columns=['nodeid','node_geom'])
Function for Writing dataframes to pgsql using sqlalchemy¶
In [5]:
from sqlalchemy import create_engine
def df_to_sql(df,tbl_name,db):
engine = create_engine('postgresql://{user}:{pw}@10.243.154.52:5432/CRASHDATA'.format(user=db.params['user'],
pw=db.params['password']),
echo=False)
df.to_sql(name='{}'.format(tbl_name), con= engine, if_exists = 'replace', index=False)
db.query("""ALTER TABLE {tbl}
ALTER COLUMN geom TYPE Geometry USING geom::Geometry;
grant all on {tbl} to public;""".format(tbl=tbl_name))
return tbl_name
Writing dead_ends dataframe to table in pgsql¶
In [ ]:
dead_ends=df_to_sql(dead_ends,'dead_ends',cdb)
Dead ends shapefile creation¶
In [6]:
db2.pg_shp.export_pg_table_to_shp(r'{path}'.format(path=os.getcwd()),
cdb,'dead_ends', shp_name='dead_ends' + str((datetime.datetime.now().strftime("_%Y-%m-%d_%H"))))
Map veiw of dead end nodes¶
In [13]:
from IPython.display import Image
PATH = "C:\Users\soge\Desktop"
Image(filename = PATH + "\dead_ends_map.png", width=1000, height=1000)
Out[13]:
Zoomed view of dead ends map¶
In [14]:
from IPython.display import Image
PATH = "C:\Users\soge\Desktop"
Image(filename = PATH + "\dead_ends_map_zoomed_2.png", width=1000, height=1000)
Out[14]:
In [ ]:
select ans.nodeid, n2.geom from (
select nodeid1 nodeid from (
select coalesce(nodeidto, nodeidfrom) nodeid1 from (
select distinct nodeidfrom from(
select distinct l.nodeidfrom, l.street, l.geom from lion l
join node n
on n.nodeid = l.nodeidfrom::int
where rb_layer in ('R', 'B', 'N')
and featuretyp not in ('1', '2', '3', '7','8', 'A', 'F')
and n.vintersect is null ) d
group by nodeidfrom
having count(nodeidfrom) = 1
) f
full join
(
select nodeidto from(
select distinct l.nodeidto, l.street, l.geom
from lion l
join node n
on n.nodeid = l.nodeidto::int
where rb_layer in ('R', 'B', 'N')
and featuretyp not in ('1', '2', '3', '7','8', 'A', 'F')
and n.vintersect is null ) d
group by nodeidto
having count(nodeidto) =1
) t
on f.nodeidfrom=t.nodeidto
where f.nodeidfrom is null or t.nodeidto is null) tot
join
(
select coalesce(nodeidto, nodeidfrom) nodeid2 from (
select distinct l.nodeidfrom --nodeidfrom table
from lion l
join node n on
n.nodeid = l.nodeidfrom::int
where l.rb_layer in ('R', 'B', 'N') and l.featuretyp not in ('1', '2', '3', '7','8', 'A', 'F')
and n.vintersect is null
) f
full join
(
select distinct l.nodeidto --nodeidto table
from lion l
join node n on
n.nodeid = l.nodeidto::int
where l.rb_layer in ('R', 'B', 'N') and l.featuretyp not in ('1', '2', '3', '7','8', 'A', 'F')
and n.vintersect is null
) t
on f.nodeidfrom=t.nodeidto
where f.nodeidfrom is null or t.nodeidto is null) tot2
on tot.nodeid1=tot2.nodeid2) ans
join node n2
on ans.nodeid::int=n2.nodeid
order by ans.nodeid